package com.sf.tools.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OracleDao implements DBUtils{

	private String driver;
	private String url;
	private String user;
	private String pass;
	
	private final String ALL_TABLES = "select a.table_name,b.comments from user_tables a,user_tab_comments b where a.table_name = b.table_name order by a.table_name";
	
	private final String TABLE_INFOS = "select a.column_name, a.data_type, "
			+ " decode(a.data_type,'NUMBER',a.data_precision,a.data_length) as data_length, a.data_scale as data_scale, "
			+ " decode(e.key,'Y','Y','N') as is_primary, "
			+ " f.comments as comments, a.nullable as nullable, a.data_default as data_default "
			+ " from user_tab_columns a, user_col_comments f, "
			+ " (select b.table_name, b.index_name,b.uniqueness, c.column_name, "
			+ " decode(d.constraint_name,NULL,'N','Y') key "
			+ " from user_indexes b, user_ind_columns c, "
			+ " ( select constraint_name from user_constraints where constraint_type='P' ) d "
			+ " where b.index_name=c.index_name and b.index_name=d.constraint_name(+) ) e "
			+ " where a.table_name=? and a.table_name=e.table_name(+) and a.column_name=e.column_name(+) "
			+ " and a.table_name=f.table_name and a.column_name=f.column_name "
			+ " order by a.column_id ";
	
	private final String PRIMARY_KEY = "select cu.column_name "
								+ "from user_cons_columns cu, user_constraints au "
								+ "where cu.constraint_name = au.constraint_name "
								+ "and au.constraint_type = 'P' "
								+ "and au.table_name = ? ";
	
	public OracleDao(String driver, String url, String user, String pass) {
		this.driver = driver;
		this.url = url;
		this.user = user;
		this.pass = pass;
	}

	/**
	 * 获取数据库中所有sequence
	 */
	public List<String> getSequences(){
		ConnectionFactory.getIntance().init(driver, url, user, pass);
		List<String> list = new ArrayList<String>();
		
		Connection conn = ConnectionFactory.getIntance().getConnection();
		PreparedStatement pstm=null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement("select sequence_name from user_sequences");
			rs = pstm.executeQuery();
			while(rs.next()){
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.getIntance().close(rs, pstm, conn);
		}
		
		return list;
	}
	
	/**
	 * 获取表的主键
	 */
	public List<String> getPrimaryKey(String tableName){
		ConnectionFactory.getIntance().init(driver, url, user, pass);
		List<String> list = new ArrayList<String>();
		
		Connection conn = ConnectionFactory.getIntance().getConnection();
		PreparedStatement pstm=null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(PRIMARY_KEY);
			pstm.setString(1, tableName);
			rs = pstm.executeQuery();
			while(rs.next()){
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.getIntance().close(rs, pstm, conn);
		}
		
		return list;
	}
	
	/**
	 * 获取所有表名
	 * @return
	 */
	public List<String[]> findAllTables(){
		ConnectionFactory.getIntance().init(driver, url, user, pass);
		List<String[]> list = new ArrayList<String[]>();
		//select * from all_tables where owner='HFLS_USER';
//		String sql = "select table_name from user_tables";
//		String sql = "select a.table_name,b.comments from user_tables a,user_tab_comments b where a.table_name = b.table_name";
		
		Connection conn = ConnectionFactory.getIntance().getConnection();
		PreparedStatement pstm=null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(ALL_TABLES);
			rs = pstm.executeQuery();
			while(rs.next()){
				String[] row = new String[2];
				for(int i=1;i<=2;i++){
//					System.out.print(rs.getString(i) + "  ");
					row[i-1] = rs.getString(i);
				}
//				System.out.println();
				list.add(row);
//System.out.println(row[0] + " - " + row[1]);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.getIntance().close(rs, pstm, conn);
		}
		return list;
	}

	public List<String[]> findTableByName(String tableName){
		ConnectionFactory.getIntance().init(driver, url, user, pass);
//		String sql = "select * from user_col_comments t where t.TABLE_NAME = upper('hfls_tm_car')";
//		StringBuffer sql = new StringBuffer();
//		sql.append("select t3.column_name,t3.data_type,t4.comments from ")
//		   .append(" (select * from user_tab_columns t where t.TABLE_NAME = upper(?)) t3, ")
//		   .append(" (select * from user_col_comments t where t.TABLE_NAME = upper(?)) t4 ")
//		   .append(" where t3.column_name = t4.column_name ")
//		   .append(" order by t3.column_id ");
		
		Connection conn = ConnectionFactory.getIntance().getConnection();
		PreparedStatement pstm=null;
		ResultSet rs = null;
		List<String[]> list = new ArrayList<String[]>();
		try {
			pstm = conn.prepareStatement(TABLE_INFOS);
			pstm.setString(1, tableName);
//			pstm.setString(2, tableName);
			rs = pstm.executeQuery();
			while(rs.next()){
//				String[] row = new String[3];
//				for(int i=1;i<=3;i++){
////					System.out.print(rs.getString(i) + "  ");
//					row[i-1] = rs.getString(i);
//				}
				
//				list.add(row);
				String[] row = new String[4];
				String columnName = rs.getString(1);
				String dataType = rs.getString(2);
				String dataLength = rs.getString(3);
				String dataScale = rs.getString(4);
				String isPrimary = rs.getString(5);
				String comments = rs.getString(6);
//				String nullable = rs.getString(7);
//				Object dataDefault = rs.getObject(8);
//System.out.println(columnName+"="+dataType+"="+dataLength+"="+dataScale+"="+isPrimary+"="+comments+"="+nullable+"="+dataDefault);
				
				row[0] = columnName;
				if("NUMBER".equals(dataType) && dataLength!=null){
					if(dataScale!=null && !"0".equals(dataScale)){
						dataType=dataType+"("+dataLength+","+dataScale+")";
					}else if(dataScale==null || "0".equals(dataScale)){
						dataType=dataType+"("+dataLength+")";
					}
				}
				if("CHAR".equals(dataType) || "VARCHAR2".equals(dataType) || "NVARCHAR2".equals(dataType)){
					dataType=dataType+"("+dataLength+")";
				}
				row[1] = dataType;
				row[2] = comments;
				row[3] = isPrimary;
				list.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.getIntance().close(rs, pstm, conn);
		}
		return list;
	}
	
	public void dataDic(){
		ConnectionFactory.getIntance().init(driver, url, user, pass);
		String sql = "select t.column_name,t.DATA_DEFAULT from user_tab_columns t where t.TABLE_NAME = upper('test')";
		Connection conn = ConnectionFactory.getIntance().getConnection();
		PreparedStatement pstm=null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){
				String columnName = rs.getString(1);
				Object value = rs.getObject(2);
				System.out.println(columnName + " - " + value);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.getIntance().close(rs, pstm, conn);
		}
	}
	
	public static void main(String[] args) {
//		new OracleDao().dataDic();
//		List<String[]> list = DBUtils.test("hfls_tm_car");
//		Object [][] userInfo = {{"11","12","13","14"},{"21","22","23","24"}};
//		userInfo = new Object[6][4];
//		
//		for(int i=0;i<6;i++){
//			for(int j=0;j<4;j++){
//				userInfo[i][j] = list.get(i)[j];
//			}
//		}
//		System.out.println("*****************************");
//		for (Object[] objects : userInfo) {
//			for (Object obj : objects) {
//				System.out.print(obj + " ");
//			}
//			System.out.println();
//		}
		
	}
}
